﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Subgurim.Controles;


public partial class Pages_Default : System.Web.UI.Page
{
    algemeen alg = new algemeen();
    List<int> lijstje = new List<int>();
    algemeen alg2 = new algemeen();
    string query1 = "";
    string query2 = "";
    int pakket_id;
    DateTime tijd = new DateTime();
    string tijdstr;
    string sleutel1;
    string sleutel2;
    //int transport_id;




    protected void Page_Load(object sender, EventArgs e)
    {
        tijd = DateTime.Now;
        if(ListBox1.Items.Count == 0)
            vul_postkantoren(ListBox1);

    }

    private void vul_postkantoren(ListBox L)
    {
        string sql_query = "select naam,locatie_id from postkantoor";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    ListItem regel = new ListItem();
                    regel.Text = (reader.GetSqlValue(0).ToString());
                    regel.Value = (reader.GetSqlValue(1).ToString());
                    L.Items.Add(regel);
                }
            }
        }
    }


    private string bepaal_afstand(string locatie_ID_1, string locatie_ID_2)
    {
        string sql_query = "EXECUTE dbo.afstand '" + locatie_ID_1 + "','" + locatie_ID_2 + "'";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    return (reader.GetSqlValue(0).ToString());
                }
            }
        }
        return "foutmelding";
    }


    private void toevoegen(string l1, string l2, int pakket_id,string sleutel2)
    {


        tijdstr = tijd.Year.ToString() + "-" + tijd.Month.ToString() + "-" + tijd.Day.ToString() + " " + tijd.Hour.ToString() + ":" + tijd.Minute.ToString() + ":" + tijd.Second.ToString();
        System.Data.SqlClient.SqlConnection sqlConnection1 =
        new System.Data.SqlClient.SqlConnection(alg.sql_string);

        

        System.Data.SqlClient.SqlDataReader lees1;
        

        sqlConnection1.Open();

        

        System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand();
        cmd1.CommandType = System.Data.CommandType.Text;

        query1 = "IF NOT EXISTS( SELECT * FROM rij_route WHERE locatie_id_van = '" + l1 + " 'AND locatie_id_naar = '" + l2 + "')";
        query1 += "UPDATE rij_route SET transport_id =null, locatie_id_van='" + l1 + "', locatie_id_naar = '" + l2 + "', tijd = '12:00' WHERE locatie_id_van = '" + l1 + " ' AND locatie_id_naar= '" + l2 + " '";
        query1 += "INSERT rij_route (transport_id, locatie_id_van, locatie_id_naar, tijd ,pakket_id ) VALUES ('" + sleutel2 + "', '" + l1 + "', '" + l2 + "', '" + tijdstr + "','" + pakket_id + "')SELECT SCOPE_IDENTITY() id ";
        query1 += " UPDATE [route]   SET [route].tijd_gepland = '" + tijdstr + "' ";
        query1 += "WHERE [route].pakket_id = '"+pakket_id+"' ";
        query1 += "AND [route].tijd_gepland is NULL AND [route].Tijd_aangekomen is NULL ";
        query1 += "and not exists(	select * ";
        query1 += "from route R2 ";
        query1 += "where R2.pakket_id = [route].pakket_id ";
        query1 += "AND R2.tijd_gepland is NULL AND R2.Tijd_aangekomen is NULL ";
        query1 += "AND R2.volgorde < [route].volgorde) ";


        tijd = tijd.AddMinutes(30);
        cmd1.CommandText = query1;
        cmd1.Connection = sqlConnection1;

        lees1 = cmd1.ExecuteReader();
        lees1.Read();
        sleutel1 = lees1.GetSqlValue(0).ToString();

        lees1.Close();



        sqlConnection1.Close();
        alg.status_update(pakket_id.ToString(), "4", false);
    }

    private void maaktabel(string locatieid)
    {


        string sql_query = "SELECT R.route_id ,R.pakket_id, case when R.locatie_best_id = '" + locatieid + "' then R.locatie_bron_id else R.locatie_best_id end as locatie ";
        sql_query += "from [route] R ";
        sql_query += "where (R.locatie_best_id = '" + locatieid + "' OR R.locatie_bron_id = '" + locatieid + "') ";
        sql_query += "AND R.tijd_gepland is NULL ";
        sql_query += "and R.tijd_aangekomen is NULL ";
        sql_query += "and not exists( select * from route R2 ";
        sql_query += "where R2.pakket_id = R.pakket_id ";
        sql_query += "AND R2.tijd_gepland is NULL ";
        sql_query += "AND R2.tijd_aangekomen is NULL ";
        sql_query += "AND R2.volgorde < R.volgorde)  ";
        sql_query += "and not exists( select * from route R3 ";
        sql_query += "where R3.pakket_id = R.pakket_id ";
        sql_query += "AND R3.tijd_gepland is not NULL ";
        sql_query += "AND R3.tijd_aangekomen is NULL) ";


        int[,] intArray;
        intArray = new int[500, 2];


        int teller = 1;
        intArray[0, 0] = int.Parse(locatieid);


        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    intArray[teller, 0] = int.Parse(reader.GetSqlValue(2).ToString());
                    intArray[teller, 1] = int.Parse(reader.GetSqlValue(1).ToString());
                    teller++;
                }
            }
        }
        if (teller == 1)
            return;





        double[,] ar = new double[500, 3];

        int tel = 0;
        for (int x = 0; x < teller; x++)
        {
            for (int y = 0; y < teller; y++)
            {
                if (x != y)
                {
                    TableRow tr = new TableRow();
                    TableCell L1 = new TableCell();
                    TableCell L2 = new TableCell();
                    L1.Text = intArray[x, 0].ToString();
                    tr.Cells.Add(L1);
                    L2.Text = intArray[y, 0].ToString();
                    tr.Cells.Add(L2);

                    TableCell cel = new TableCell();
                    ar[tel, 0] = intArray[x, 0];
                    ar[tel, 1] = intArray[y, 0];
                    ar[tel, 2] = double.Parse(bepaal_afstand(intArray[x, 0].ToString(), intArray[y, 0].ToString()));
                    cel.Text = ar[tel, 2].ToString();
                    tr.Cells.Add(cel);
                    tabel.Rows.Add(tr);
                    tel++;
                }
            }
        }


        int actueel = int.Parse(locatieid);
        int Nactueel;
        double[,] rr = new double[500, 3];



        for (int x = 0; x < teller; x++)
        {

            Nactueel = bepaal_kort(ar, actueel, teller);
            rr[x, 0] = actueel;
            rr[x, 1] = Nactueel;
            actueel = Nactueel;
        }
        double totaal = 0.0;


        System.Data.SqlClient.SqlConnection sqlConnection1 =
        new System.Data.SqlClient.SqlConnection(alg.sql_string);
        System.Data.SqlClient.SqlDataReader lees2;

        System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand();
        cmd2.CommandType = System.Data.CommandType.Text;
        query2 = "INSERT INTO transport (postkantoren_id, naam) VALUES ('1', 'transport op " + DateTime.Now.ToString() + "') SELECT SCOPE_IDENTITY() id";
        cmd2.CommandText = query2;
        cmd2.Connection = sqlConnection1;

        sqlConnection1.Open();

        lees2 = cmd2.ExecuteReader();
        lees2.Read();
        sleutel2 = lees2.GetSqlValue(0).ToString();
        lees2.Close();



        for (int x = 0; x < teller; x++)
        {
            TableRow tr = new TableRow();
            TableCell L1 = new TableCell();
            TableCell L2 = new TableCell();
            TableCell L3 = new TableCell();
            L1.Text = rr[x, 0].ToString();
            tr.Cells.Add(L1);
            L2.Text = rr[x, 1].ToString();
            tr.Cells.Add(L2);
            L3.Text = bepaal_afstand(L1.Text, L2.Text);
            totaal += double.Parse(L3.Text);
            tr.Cells.Add(L3);
            tabel2.Rows.Add(tr);


            pakket_id = 0;
            for (int y = 0; y < 500; y++)
                if (intArray[y, 0] == int.Parse(rr[x, 1].ToString()))
                    pakket_id = intArray[y, 1];


            toevoegen(L1.Text, L2.Text, pakket_id,sleutel2);
        }
        TableRow tra = new TableRow();
        TableCell L1a = new TableCell();
        L1a.Text = totaal.ToString();
        tra.Cells.Add(L1a);
        tabel2.Rows.Add(tra);


    }



    private int bepaal_kort(double[,] ar, int x, int teller)
    {
        double lw;
        lw = 99999.99;
        int uitkomst = 0;
        for (int q = 0; q < 500; q++)
        {
            if ((ar[q, 0] == x) && !(lijstje.Contains(int.Parse(ar[q, 1].ToString()))))
            {
                if (ar[q, 2] < lw)
                {
                    lw = ar[q, 2];
                    uitkomst = int.Parse(ar[q, 1].ToString());
                }
            }
        }
        lijstje.Add(uitkomst);
        return uitkomst;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        if (Informatie.Checked == true)
        {
            tabel.Visible = true;
            tabel2.Visible = true;
            Label1.Visible = true;
        }
        else
        {
            tabel.Visible = false;
            tabel2.Visible = false;
            Label1.Visible = false;
        }
        maaktabel(ListBox1.SelectedValue);

    }
    protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string sql_query = "select pc_van,pc_tot from postkantoor where locatie_id = '"+ListBox1.SelectedValue.ToString()+"'";
        using (SqlConnection connection = new SqlConnection(alg.sql_string))
        using (SqlCommand command = new SqlCommand(sql_query, connection))
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    pc_van.Text = (reader.GetSqlValue(0).ToString());
                    pc_tot.Text = (reader.GetSqlValue(1).ToString());
                }
            }
        }
    }
}